package com.xxkk.spring_boot_web.utils;

/**
 * @author xxkk
 * @version 1.0.0
 * @title MySQLTableUpdater
 * @description ʕ•ᴥ•ʔ
 * @create 2025/1/11 09:46
 **/

import com.alibaba.fastjson.JSONObject;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MySQLSyncUtil {
    public static void main(String[] args) {
        String url = "jdbc:mysql://192.168.31.88:3306/pay_db";
        String username = "root";
        String password = "123456";
        MySQLSyncUtil.generator(url, username, password);
//        MysqlSyncUtil.update(url, username, password);
    }

    /**
     * Mysql 同步工具执行同步
     *
     * @param jdbcUrl
     * @param username
     * @param password
     */
    public static void update(String jdbcUrl, String username, String password) {
        update(jdbcUrl, username, password, new File("").getAbsolutePath() + "/MySQLSync.json");
    }

    public static void update(String jdbcUrl, String username, String password, String mysqlSyncDateJsonFilePath) {
        System.out.println("MySQL Sync file path:\n" + mysqlSyncDateJsonFilePath);
        String json = readFromFile(mysqlSyncDateJsonFilePath);
        if (json == null) {
            return;
        }

        String url = extractUrlAndDbName(jdbcUrl)[0];
        XKDB xkdb = JSONObject.parseObject(json, XKDB.class);
        String dbName = xkdb.dbName;

        try {// 加载 MySQL JDBC 驱动程序
            Class.forName("com.mysql.jdbc.Driver"); // 对于 MySQL Connector/J 5.x
            Connection initialConnection = DriverManager.getConnection(url, username, password); // 建立初始连接（不指定数据库）
            System.out.println("mysql数据库同步工具 成功连接数据库");
            ResultSet resultSet = initialConnection.getMetaData().getCatalogs();
            boolean dbExist = false; // 检查数据库是否存在
            while (resultSet.next()) {
                if (resultSet.getString(1).equals(dbName)) {// 如果数据库不存在，则创建数据库
                    dbExist = true;
                    break;
                }
            }
            resultSet.close();
            if (!dbExist) {
                Statement stmt = initialConnection.createStatement();
                stmt.executeUpdate(String.format("CREATE DATABASE IF NOT EXISTS %s", dbName));
                stmt.close();
                System.out.println("数据库 " + dbName + " 已创建。");
            }
            initialConnection.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        doUpdate0(jdbcUrl, xkdb, username, password);
    }

    private static String[] extractUrlAndDbName(String url) {
        // 正则表达式模式匹配URL头部和数据库名称，支持带查询参数的情况
        String pattern = "^(jdbc:mysql://[\\w\\.-]+(:\\d+)?/)(\\w+)(\\?.*)?$";
        String[] str_ls = new String[3];
        Pattern compiledPattern = Pattern.compile(pattern);
        Matcher matcher = compiledPattern.matcher(url);
        if (matcher.find()) {
            String urlHead = str_ls[0] = matcher.group(1);
            String dbName = str_ls[1] = matcher.group(3);
            String queryParams = str_ls[2] = matcher.group(4) != null ? matcher.group(4) : "";
            System.out.println("Extracted URL Head: " + urlHead);
            System.out.println("Extracted DB Name: " + dbName);

            if (!queryParams.isEmpty()) { // 去掉问号并分离参数
                queryParams = queryParams.substring(1); // 去掉问号
                String[] params = queryParams.split("&");
                System.out.println("Query Params:");
                for (String param : params) {
                    System.out.println("  " + param);
                }
            }
        } else
            System.out.println("MySQL JDBC url No match found for URL: " + url);
        return str_ls;
    }

    private static void doUpdate0(String jdbcUrl, XKDB xkdb, String username, String password) {
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            updateTables(xkdb.dbName, xkdb.tableList, connection);
            System.out.println("mysql数据库结构同步完成:" + xkdb.dbName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 检查表是否存在
    private static void updateTables(String dbName, List<XKTable> list, Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        try (Statement stmt = connection.createStatement()) {
            for (XKTable table : list) {
                String tableName = table.tableName;
                // 表检查
                if (isTableExists(metaData, tableName)) {
                    for (XKColumn xkColumn : table.columnList) {
                        String columnName = xkColumn.name;
                        String type = xkColumn.type;
                        String attributes = xkColumn.attributes;
                        if (!isColumnExists(metaData, tableName, columnName)) { // 字段不存在，生成并执行 ALTER TABLE 语句
                            String alterTableSQL = String.format("ALTER TABLE `%s`.`%s` ADD COLUMN `%s` %s %s", dbName, tableName, columnName, type, attributes);
                            stmt.executeUpdate(alterTableSQL);
                            System.out.println("添加字段 '" + columnName + "' : " + alterTableSQL);
                        }
                    }
                } else {// 表不存在，创建表
                    stmt.executeUpdate(table.createTableSQL);
                    System.out.println("表 '" + table.tableName + "' 已创建。");
                    continue;
                }
                // 判断索引
                for (XKSqlIndex sqlIndex : table.sqlIndexList) {
                    if (!isIndexExists(stmt, tableName, sqlIndex.keyName)) {
                        String createIndexSQL = String.format("CREATE INDEX %s ON %s (%s)", sqlIndex.keyName, tableName, sqlIndex.keyColumns);
                        stmt.executeUpdate(createIndexSQL); // 创建索引
                        System.out.println("创建索引 " + sqlIndex.keyName + " :" + createIndexSQL);
                    }
                }

            }
        }
    }

    // 检查索引是否存在
    private static boolean isIndexExists(Statement stmt, String tableName, String indexName) throws SQLException {
        String checkIndexSQL = String.format(
                "SELECT COUNT(1) FROM information_schema.statistics " +
                        "WHERE table_schema = DATABASE() AND table_name = '%s' AND index_name = '%s'",
                tableName, indexName
        );
        try (ResultSet rs = stmt.executeQuery(checkIndexSQL)) {
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        }
    }

    private static boolean isTableExists(DatabaseMetaData metaData, String tableName) throws SQLException {
        try (ResultSet rs = metaData.getTables(null, null, tableName, new String[]{"TABLE"});) {
            return rs.next();
        }
    }

    private static boolean isColumnExists(DatabaseMetaData metaData, String tableName, String columnName) throws SQLException {
        try (ResultSet rs = metaData.getColumns(null, null, tableName, columnName)) {
            return rs.next();
        }
    }

    /**
     * 生成Mysql数据库同步文件
     *
     * @param jdbcUrl
     * @param username
     * @param password
     */
    public static void generator(String jdbcUrl, String username, String password) {
        generator(jdbcUrl, username, password, new File("").getAbsolutePath() + "/MySQLSync.json");
    }

    public static void generator(String jdbcUrl, String username, String password, String filePath) {
        try {// 加载 MySQL JDBC 驱动程序
            Class.forName("com.mysql.jdbc.Driver"); // 对于 MySQL Connector/J 5.x
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        String dbName = extractUrlAndDbName(jdbcUrl)[1];
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            List<XKTable> tableVoList = generatorTable(connection);
            XKDB xkdb = new XKDB(dbName, tableVoList);
            String json = JSONObject.toJSONString(xkdb);
            json = formatJson(json);
            writeToFile(filePath, json);
            System.out.println("Table structure updated successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static List<XKTable> generatorTable(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();  // 获取数据库元数据
        ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
        List<XKTable> tableVoList = new ArrayList<>();
        Statement stmt = connection.createStatement();
        // 遍历所有表并获取创建语句
        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
            if (rs.next()) {
                String createTableSQL = rs.getString("Create Table");
                // 替换 "CREATE TABLE" 为 "CREATE TABLE IF NOT EXISTS"
                createTableSQL = createTableSQL.replaceFirst("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
                System.out.println("Table: " + tableName);
                XKTable tableVo = new XKTable(tableName, createTableSQL);
                tableVoList.add(tableVo);
            }
            rs.close();
        }
        stmt.close();
        return tableVoList;
    }

    public static class XKDB {
        public String dbName;
        //        public String createDBSQL;
        public List<XKTable> tableList;

        public XKDB(String dbName, List<XKTable> tableList) {
            this.dbName = dbName;
//            this.createDBSQL = String.format("CREATE DATABASE IF NOT EXISTS %s", dbName);
            this.tableList = tableList;
        }

        public XKDB() {
        }
    }


    private static class XKTable {
        public String tableName;//表名 Table: example
        public String createTableSQL;//建表语句
        public List<XKColumn> columnList;//拆分的表字段内容
        public String primaryKeyName;//主键索引
        public List<XKSqlIndex> sqlIndexList;//其它索引

        XKTable(String tableName, String createTableSQL) {
            this.tableName = tableName;
            this.createTableSQL = createTableSQL;
            parseCreateTableSQL(createTableSQL);
        }

        public XKTable() {
        }

        public void parseCreateTableSQL(String sql) {
            // 正则表达式匹配字段定义
            String fieldPattern = "`(\\w+)`\\s+(\\w+\\(\\d+\\)|\\w+\\(\\d+,\\d+\\)|\\w+)\\s*(.*?),\n";
            Pattern pattern = Pattern.compile(fieldPattern);
            Matcher matcher = pattern.matcher(sql);
            List<XKColumn> columns = new ArrayList<>();
            while (matcher.find()) {
                String fieldName = matcher.group(1);
                String fieldType = matcher.group(2);
                String fieldAttributes = matcher.group(3).trim();
                XKColumn column = new XKColumn(fieldName, fieldType, fieldAttributes);
                columns.add(column);
                System.out.println("Field Name: " + fieldName);
                System.out.println("Field Type: " + fieldType);
                System.out.println("Field Attributes: " + fieldAttributes);
                System.out.println();
            }
            this.columnList = columns;

            // 正则表达式匹配主键定义
            String primaryKeyPattern = "PRIMARY KEY \\(`(\\w+)`\\)";
            Pattern primaryKeyPatternCompiled = Pattern.compile(primaryKeyPattern);
            Matcher primaryKeyMatcher = primaryKeyPatternCompiled.matcher(sql);
            while (primaryKeyMatcher.find()) {
                String primaryKey = primaryKeyMatcher.group(1);
                this.primaryKeyName = primaryKey;
                System.out.println("Primary Key: " + primaryKey);
                System.out.println();
            }

            // 正则表达式匹配普通索引定义
            String keyPattern = "KEY `(\\w+)` \\(([^)]+)\\)";
            Pattern keyPatternCompiled = Pattern.compile(keyPattern);
            Matcher keyMatcher = keyPatternCompiled.matcher(sql);
            List<XKSqlIndex> sqlIndexes = new ArrayList<>();
            while (keyMatcher.find()) {
                String keyName = keyMatcher.group(1);
                String keyColumn = keyMatcher.group(2);
                XKSqlIndex sqlIndex = new XKSqlIndex(keyName, keyColumn);
                sqlIndexes.add(sqlIndex);
                System.out.println("Key Name: " + keyName);
                System.out.println("Key Column: " + keyColumn);
                System.out.println();
            }
            this.sqlIndexList = sqlIndexes;
        }
    }

    private static class XKColumn {
        public String name;//字段属性名  如:id
        public String type;//字段类型 如:int(11)
        public String attributes;//字段其它属性定义
//        public String alterTableSQL;//添加字段语句 如: "ALTER TABLE `example` ADD COLUMN `columnName` int(11) NOT NULL;

        public XKColumn(String columnName, String type, String attributes) {
            this.name = columnName;
            this.type = type;
            this.attributes = attributes;
//            this.alterTableSQL = String.format("ALTER TABLE `%s`.`%s` ADD COLUMN `%s` %s %s", db, table, columnName, type, attributes);
        }

        public XKColumn() {
        }
    }

    /**
     * Mysql 数据库表索引数据结构
     */
    private static class XKSqlIndex {
        public String keyName;//索引名称
        public String keyColumns;//索引属性

        XKSqlIndex(String keyName, String keyColumns) {
            this.keyName = keyName;
            this.keyColumns = keyColumns;
        }

        public XKSqlIndex() {
        }
    }

    // 格式化 JSON 字符串
    public static String formatJson(String jsonString) {
        StringBuilder formattedJson = new StringBuilder();
        int indentLevel = 0;
        boolean inQuotes = false;

        for (char charFromJson : jsonString.toCharArray()) {
            switch (charFromJson) {
                case '"':
                    formattedJson.append(charFromJson);
                    inQuotes = !inQuotes;
                    break;
                case ' ':
                    if (inQuotes) {
                        formattedJson.append(charFromJson);
                    }
                    break;
                case '{':
                case '[':
                    formattedJson.append(charFromJson);
                    if (!inQuotes) {
                        formattedJson.append("\n");
                        indentLevel++;
                        addIndentation(formattedJson, indentLevel);
                    }
                    break;
                case '}':
                case ']':
                    if (!inQuotes) {
                        formattedJson.append("\n");
                        indentLevel--;
                        addIndentation(formattedJson, indentLevel);
                    }
                    formattedJson.append(charFromJson);
                    break;
                case ',':
                    formattedJson.append(charFromJson);
                    if (!inQuotes) {
                        formattedJson.append("\n");
                        addIndentation(formattedJson, indentLevel);
                    }
                    break;
                case ':':
                    formattedJson.append(charFromJson);
                    if (!inQuotes) {
                        formattedJson.append(" ");
                    }
                    break;
                default:
                    formattedJson.append(charFromJson);
                    break;
            }
        }

        return formattedJson.toString();
    }

    // 添加缩进
    private static void addIndentation(StringBuilder sb, int indentLevel) {
        for (int i = 0; i < indentLevel; i++) {
            sb.append("    "); // 增加4个空格的缩进
        }
    }

    public static String readFromFile(String filePath) {
        File file = new File(filePath);
        if (!file.exists())
            return null;
        StringBuilder contentBuilder = new StringBuilder();
        try (BufferedReader reader = new BufferedReader(new FileReader(file))) {
            String line;
            while ((line = reader.readLine()) != null) {
                contentBuilder.append(line).append("\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return contentBuilder.toString();
    }

    public static void writeToFile(String filePath, String data) {
//       FileWriter writer_ = new FileWriter(filePath);
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            writer.write(data);
            writer.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}